from src.db_util import DBUtil, resource_path
import logging
import os
import sqlite3
import sys

def resource_path(relative_path):
    """ 获取资源的绝对路径，适用于开发环境和PyInstaller打包后的环境 """
    try:
        if hasattr(sys, '_MEIPASS'):
            # PyInstaller
            if relative_path == 'pms.db':
                # 数据库文件应该在exe所在目录
                return os.path.join(os.path.dirname(sys.executable), relative_path)
            else:
                # 其他资源文件在临时目录
                return os.path.join(sys._MEIPASS, relative_path)
        else:
            # 以 src 上级目录为根
            return os.path.join(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')), relative_path)
    except Exception as e:
        logging.error(f"Error in resource_path for '{relative_path}': {e}, using fallback CWD.")
        # Fallback to CWD in case of any error.
        return os.path.join(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')), relative_path)

def init_database():
    """初始化数据库，创建表并插入初始数据"""
    db = DBUtil()
    
    # 测试连接
    logging.info("正在测试数据库连接...")
    
    # 确保每次连接后都关闭
    if not db.test_connection():
        logging.info("数据库文件不存在，将创建新数据库...")
        
        # 获取数据库文件路径
        db_file_path = resource_path('pms.db')
        db_dir = os.path.dirname(db_file_path)
        if not os.path.exists(db_dir):
            try:
                os.makedirs(db_dir)
                logging.info(f"创建目录: {db_dir}")
            except Exception as e:
                logging.error(f"创建目录 {db_dir} 失败: {e}")
                return False
        
        try:
            temp_conn = sqlite3.connect(db_file_path)
            temp_conn.close()
            logging.info(f"数据库文件 {db_file_path} 创建成功!")
        except Exception as e:
            logging.error(f"创建数据库文件 {db_file_path} 失败: {e}")
            return False

        # 再次测试连接
        if not db.test_connection():
            logging.error(f"无法连接到数据库 {db.database}，即使在尝试创建后!")
            return False
    
    # 连接数据库
    db.connect()
    
    # 建表SQL语句 - 每个表创建为单独的语句
    create_tables_sqls = [
        # 部署位置类型表
        "CREATE TABLE IF NOT EXISTS PMS_DEPLOY_TYPE (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT);",
        # 创建部署位置类型名称唯一索引
        "CREATE UNIQUE INDEX IF NOT EXISTS uk_deploy_type_name ON PMS_DEPLOY_TYPE (name);",
        
        # 项目分类表
        "CREATE TABLE IF NOT EXISTS PMS_PROJECT_CATEGORY (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT);",
        # 创建项目分类名称唯一索引
        "CREATE UNIQUE INDEX IF NOT EXISTS uk_project_category_name ON PMS_PROJECT_CATEGORY (name);",
        
        # 项目状态表
        "CREATE TABLE IF NOT EXISTS PMS_STATUS (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, description TEXT);",
        # 创建项目状态名称唯一索引
        "CREATE UNIQUE INDEX IF NOT EXISTS uk_status_name ON PMS_STATUS (name);",
        
        # 用户表
        """CREATE TABLE IF NOT EXISTS PMS_USER (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          username TEXT NOT NULL,
          password TEXT NOT NULL,
          real_name TEXT,
          email TEXT,
          phone TEXT,
          create_time TIMESTAMP,
          last_login_time TIMESTAMP,
          status INTEGER NOT NULL DEFAULT 1
        );""",
        # 创建用户名唯一索引
        "CREATE UNIQUE INDEX IF NOT EXISTS uk_username ON PMS_USER (username);",
        
        # 项目表
        """CREATE TABLE IF NOT EXISTS PMS_PROJECT (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          code TEXT NOT NULL,
          category_id INTEGER NOT NULL,
          git_url TEXT,
          description TEXT,
          status_id INTEGER NOT NULL,
          create_user_id INTEGER,
          create_time TIMESTAMP,
          update_user_id INTEGER,
          update_time TIMESTAMP
        );""",
        # 创建项目代码唯一索引及其他索引
        "CREATE UNIQUE INDEX IF NOT EXISTS uk_code ON PMS_PROJECT (code);",
        "CREATE INDEX IF NOT EXISTS idx_category ON PMS_PROJECT (category_id);",
        "CREATE INDEX IF NOT EXISTS idx_status ON PMS_PROJECT (status_id);",
        
        # 部署信息表
        """CREATE TABLE IF NOT EXISTS PMS_DEPLOYMENT (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          project_id INTEGER NOT NULL,
          deploy_type_id INTEGER NOT NULL,
          host_ip TEXT NOT NULL,
          username TEXT,
          directory TEXT NOT NULL,
          start_command TEXT,
          stop_command TEXT,
          status_check_command TEXT,
          log_path TEXT,
          description TEXT,
          create_user_id INTEGER,
          create_time TIMESTAMP,
          update_user_id INTEGER,
          update_time TIMESTAMP
        );""",
        # 创建部署相关索引
        "CREATE INDEX IF NOT EXISTS idx_project ON PMS_DEPLOYMENT (project_id);",
        "CREATE INDEX IF NOT EXISTS idx_deploy_type ON PMS_DEPLOYMENT (deploy_type_id);"
    ]
    
    # 执行建表SQL
    logging.info("开始创建表...")
    for sql in create_tables_sqls:
        result = db.execute_update(sql)
        if result is None and result != 0:  # SQLite可能返回0表示无行影响，这是正常的
            logging.error(f"创建表失败! SQL: {sql}")
            return False
    
    logging.info("表创建成功!")
    
    # 检查基础表是否有数据，如果有则不初始化
    check_data = db.execute_query("SELECT COUNT(*) as count FROM PMS_USER")
    if check_data and check_data[0]['count'] > 0:
        logging.info("数据库已包含数据，跳过初始化数据步骤...")
        db.close()
        return True
    
    # 初始化数据
    init_data_sqls = [
        # 部署类型初始数据
        """INSERT INTO PMS_DEPLOY_TYPE (name, description)
        SELECT '本机', '部署在本地服务器上'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_DEPLOY_TYPE WHERE name = '本机');""",
        
        """INSERT INTO PMS_DEPLOY_TYPE (name, description)
        SELECT '内网', '部署在公司内网服务器上' 
        WHERE NOT EXISTS (SELECT 1 FROM PMS_DEPLOY_TYPE WHERE name = '内网');""",
        
        """INSERT INTO PMS_DEPLOY_TYPE (name, description)
        SELECT '私有云', '部署在公司私有云上'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_DEPLOY_TYPE WHERE name = '私有云');""",
        
        """INSERT INTO PMS_DEPLOY_TYPE (name, description)
        SELECT '公网云', '部署在公网云服务上'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_DEPLOY_TYPE WHERE name = '公网云');""",
        
        # 项目分类初始数据
        """INSERT INTO PMS_PROJECT_CATEGORY (name, description)
        SELECT '核心系统', '公司核心业务系统'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_PROJECT_CATEGORY WHERE name = '核心系统');""",
        
        """INSERT INTO PMS_PROJECT_CATEGORY (name, description)
        SELECT '重要系统', '公司重要业务系统'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_PROJECT_CATEGORY WHERE name = '重要系统');""",
        
        """INSERT INTO PMS_PROJECT_CATEGORY (name, description)
        SELECT '工具软件或简单web', '辅助工作的软件或简单功能的web系统'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_PROJECT_CATEGORY WHERE name = '工具软件或简单web');""",
        
        """INSERT INTO PMS_PROJECT_CATEGORY (name, description)
        SELECT '定时任务', '定时执行的任务程序'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_PROJECT_CATEGORY WHERE name = '定时任务');""",
        
        """INSERT INTO PMS_PROJECT_CATEGORY (name, description)
        SELECT '临时补丁程序', '临时性问题修复程序'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_PROJECT_CATEGORY WHERE name = '临时补丁程序');""",
        
        # 项目状态初始数据
        """INSERT INTO PMS_STATUS (name, description)
        SELECT '开发中', '项目处于开发阶段'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_STATUS WHERE name = '开发中');""",
        
        """INSERT INTO PMS_STATUS (name, description)
        SELECT '测试中', '项目处于测试阶段'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_STATUS WHERE name = '测试中');""",
        
        """INSERT INTO PMS_STATUS (name, description)
        SELECT '已上线', '项目已经上线运行'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_STATUS WHERE name = '已上线');""",
        
        """INSERT INTO PMS_STATUS (name, description)
        SELECT '下线停用', '项目已下线停止使用'
        WHERE NOT EXISTS (SELECT 1 FROM PMS_STATUS WHERE name = '下线停用');""",
        
        # 管理员用户初始数据
        """INSERT INTO PMS_USER (username, password, real_name, status, create_time)
        SELECT 'admin', '123456', '系统管理员', 1, datetime('now')
        WHERE NOT EXISTS (SELECT 1 FROM PMS_USER WHERE username = 'admin');"""
    ]
    
    # 执行初始化数据SQL
    logging.info("开始初始化数据...")
    for sql in init_data_sqls:
        result = db.execute_update(sql)
        if result is None and result != 0:  # SQLite可能返回0表示无行影响，这是正常的
            logging.error(f"初始化数据失败! SQL: {sql}")
            return False
    
    logging.info("数据初始化成功!")
    
    # 验证数据是否正确初始化
    tables = ["PMS_DEPLOY_TYPE", "PMS_PROJECT_CATEGORY", "PMS_STATUS", "PMS_USER"]
    for table in tables:
        count = db.execute_query(f"SELECT COUNT(*) as count FROM {table}")
        if count and count[0]['count'] > 0:
            logging.info(f"表 {table} 数据初始化验证成功，共有 {count[0]['count']} 条记录")
        else:
            logging.warning(f"表 {table} 数据初始化验证失败，没有数据")
    
    db.close()
    return True

def init_db():
    try:
        print("开始初始化数据库...")
        db_path = resource_path('dist/pms.db')
        print(f"数据库路径: {db_path}")
        
        # 确保目录存在
        db_dir = os.path.dirname(db_path)
        if not os.path.exists(db_dir):
            print(f"创建目录: {db_dir}")
            os.makedirs(db_dir)
        
        # 连接数据库（如果不存在会自动创建）
        print("连接数据库...")
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        existing_tables = set(table[0] for table in cursor.fetchall())
        
        # 1. 创建部署类型表（如果不存在）
        if 'PMS_DEPLOY_TYPE' not in existing_tables:
            print("创建部署类型表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_DEPLOY_TYPE (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT
            )''')
            cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS uk_deploy_type_name ON PMS_DEPLOY_TYPE (name)')
            
            # 检查是否需要插入初始数据
            cursor.execute('SELECT COUNT(*) FROM PMS_DEPLOY_TYPE')
            if cursor.fetchone()[0] == 0:
                print("插入部署类型初始数据...")
                cursor.executemany('INSERT INTO PMS_DEPLOY_TYPE (name, description) VALUES (?, ?)', [
                    ('本机', '部署在本地服务器上'),
                    ('内网', '部署在公司内网服务器上'),
                    ('私有云', '部署在公司私有云上'),
                    ('公网云', '部署在公网云服务上')
                ])
        
        # 2. 创建项目分类表（如果不存在）
        if 'PMS_PROJECT_CATEGORY' not in existing_tables:
            print("创建项目分类表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_PROJECT_CATEGORY (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT
            )''')
            cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS uk_project_category_name ON PMS_PROJECT_CATEGORY (name)')
            
            # 检查是否需要插入初始数据
            cursor.execute('SELECT COUNT(*) FROM PMS_PROJECT_CATEGORY')
            if cursor.fetchone()[0] == 0:
                print("插入项目分类初始数据...")
                cursor.executemany('INSERT INTO PMS_PROJECT_CATEGORY (name, description) VALUES (?, ?)', [
                    ('核心系统', '公司核心业务系统'),
                    ('重要系统', '公司重要业务系统'),
                    ('工具软件或简单web', '辅助工作的软件或简单功能的web系统'),
                    ('定时任务', '定时执行的任务程序'),
                    ('临时补丁程序', '临时性问题修复程序')
                ])
        
        # 3. 创建状态表（如果不存在）
        if 'PMS_STATUS' not in existing_tables:
            print("创建状态表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_STATUS (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT
            )''')
            cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS uk_status_name ON PMS_STATUS (name)')
            
            # 检查是否需要插入初始数据
            cursor.execute('SELECT COUNT(*) FROM PMS_STATUS')
            if cursor.fetchone()[0] == 0:
                print("插入状态初始数据...")
                cursor.executemany('INSERT INTO PMS_STATUS (name, description) VALUES (?, ?)', [
                    ('开发中', '项目处于开发阶段'),
                    ('测试中', '项目处于测试阶段'),
                    ('已上线', '项目已经上线运行'),
                    ('下线停用', '项目已下线停止使用')
                ])
        
        # 4. 创建用户表（如果不存在）
        if 'PMS_USER' not in existing_tables:
            print("创建用户表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_USER (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                real_name TEXT,
                email TEXT,
                phone TEXT,
                create_time TIMESTAMP,
                last_login_time TIMESTAMP,
                status INTEGER NOT NULL DEFAULT 1
            )''')
            cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS uk_username ON PMS_USER (username)')
            
            # 检查是否需要插入初始数据
            cursor.execute('SELECT COUNT(*) FROM PMS_USER')
            if cursor.fetchone()[0] == 0:
                print("插入管理员用户...")
                cursor.execute('''
                INSERT INTO PMS_USER (username, password, real_name, status, create_time)
                VALUES (?, ?, ?, ?, datetime('now'))
                ''', ('admin', '123456', '系统管理员', 1))
        
        # 5. 创建项目表（如果不存在）
        if 'PMS_PROJECT' not in existing_tables:
            print("创建项目表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_PROJECT (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                code TEXT NOT NULL,
                category_id INTEGER NOT NULL,
                git_url TEXT,
                description TEXT,
                status_id INTEGER NOT NULL,
                create_user_id INTEGER,
                create_time TIMESTAMP,
                update_user_id INTEGER,
                update_time TIMESTAMP
            )''')
            cursor.execute('CREATE UNIQUE INDEX IF NOT EXISTS uk_code ON PMS_PROJECT (code)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_category ON PMS_PROJECT (category_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_status ON PMS_PROJECT (status_id)')
        
        # 6. 创建部署信息表（如果不存在）
        if 'PMS_DEPLOYMENT' not in existing_tables:
            print("创建部署信息表...")
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS PMS_DEPLOYMENT (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                deploy_type_id INTEGER NOT NULL,
                host_ip TEXT NOT NULL,
                username TEXT,
                directory TEXT NOT NULL,
                start_command TEXT,
                stop_command TEXT,
                status_check_command TEXT,
                log_path TEXT,
                description TEXT,
                create_user_id INTEGER,
                create_time TIMESTAMP,
                update_user_id INTEGER,
                update_time TIMESTAMP
            )''')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_project ON PMS_DEPLOYMENT (project_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_deploy_type ON PMS_DEPLOYMENT (deploy_type_id)')
        
        print("提交数据库更改...")
        conn.commit()
        
        print("关闭数据库连接...")
        conn.close()
        
        print("数据库初始化完成！")
        return True
        
    except Exception as e:
        print(f"数据库初始化失败: {str(e)}")
        logging.error(f"数据库初始化失败: {str(e)}")
        if 'conn' in locals():
            try:
                conn.rollback()
                conn.close()
            except:
                pass
        return False

if __name__ == "__main__":
    success = init_database()
    if success:
        print("数据库初始化完成！")
    else:
        print("数据库初始化失败！") 